using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{

    public partial class VGASTGA
    {
        #region
        #endregion
        public void SetUpNewRow(ref DataRow dwReturn, DataRow dwInput = null)
        {
            switch (app.company)
            {
                case "FLT":
                    SResult = QueryGASTGA(dwInput, 0);
                    dwReturn = SResult.Tables[0].Rows[0];
                    #region
                    dwReturn["GAGMANDANT"] = "00";
                    dwReturn["GAGART"] = "400029.NM.18.1      ";
                    dwReturn["GAGVAR"] = "1  ";
                    dwReturn["GAGFARBE"] = "7F1610    ";
                    dwReturn["GAGPOS"] = "01";
                    dwReturn["GAGGART"] = "400029.NM.18.1      ";
                    dwReturn["GAGGVAR"] = "   ";
                    dwReturn["GAGGFARBE"] = "000000    ";
                    dwReturn["GAGABF"] = "0";
                    dwReturn["GAGKGNET"] = "0001000000";
                    dwReturn["GAGKGBRUT"] = "0000000000";
                    dwReturn["GAGDISPOKZ"] = "B";
                    dwReturn["GAGBEM"] = "                    ";
                    dwReturn["GAGFDZANZ"] = "1";
                    dwReturn["GAGANTEIL"] = "100000";
                    dwReturn["GAGBASIS"] = "10000";
                    dwReturn["GAGFILLER"] = "                                   ";
                    #endregion
                    break;
                case "YGR":
                case "TST":
                case "SXN":
                case "UBT":
                default:
                    #region=========
                    dwReturn["GAGMANDANT"] = "00";
                    dwReturn["GAGART"] = "                    ";
                    dwReturn["GAGVAR"] = "   ";
                    dwReturn["GAGFARBE"] = "          ";
                    dwReturn["GAGPOS"] = "01";
                    dwReturn["GAGGART"] = "                    ";
                    dwReturn["GAGGVAR"] = "   ";
                    dwReturn["GAGGFARBE"] = "000000    ";
                    dwReturn["GAGABF"] = "0";
                    dwReturn["GAGKGNET"] = "0001000000";
                    dwReturn["GAGKGBRUT"] = "0000000000";
                    dwReturn["GAGDISPOKZ"] = "B";
                    dwReturn["GAGBEM"] = "                    ";
                    dwReturn["GAGFDZANZ"] = "1";
                    dwReturn["GAGANTEIL"] = "100000";
                    dwReturn["GAGBASIS"] = "10000";
                    dwReturn["GAGFILLER"] = "                                   ";
                    #endregion
                    #region=========
                    #endregion
                    break;
            }
            INTEXDBC.MapData(true, ref dwReturn, dwInput);
        }
        public DataRow Convert2GASTGA(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryGASTGA(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
                    dwReturn["GAGMANDANT"] = " ".PadRight(2);
                    dwReturn["GAGART"] = " ".PadRight(20);
                    dwReturn["GAGVAR"] = " ".PadRight(3);
                    dwReturn["GAGFARBE"] = " ".PadRight(10);
                    dwReturn["GAGPOS"] = " ".PadRight(2);
                    dwReturn["GAGGART"] = " ".PadRight(20);
                    dwReturn["GAGGVAR"] = " ".PadRight(3);
                    dwReturn["GAGGFARBE"] = " ".PadRight(10);
                    dwReturn["GAGABF"] = " ".PadRight(0);
                    dwReturn["GAGKGNET"] = " ".PadRight(10);
                    dwReturn["GAGKGBRUT"] = " ".PadRight(10);
                    dwReturn["GAGDISPOKZ"] = " ".PadRight(1);
                    dwReturn["GAGBEM"] = " ".PadRight(20);
                    dwReturn["GAGFDZANZ"] = " ".PadRight(0);
                    dwReturn["GAGANTEIL"] = " ".PadRight(0);
                    dwReturn["GAGBASIS"] = " ".PadRight(0);
                    dwReturn["GAGFILLER"] = " ".PadRight(35);
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapGASTGA(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void DeleteGASTGA(DataRow dwData, int option = 1)
        {

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM GASTGA   WHERE  GAGMANDANT=:GAGMANDANT: AND GAGART=:GAGART: AND GAGVAR =:GAGVAR: AND GAGFARBE=:GAGFARBE: AND GAGPOS=:GAGPOS:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public void DGG00(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();

            or1.CommandText = "DELETE FROM GASTGA "
                + " WHERE  GAGMANDANT=:GAGMANDANT:  ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void DGG01(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();

            or1.CommandText = "DELETE FROM GASTGA "
                + " WHERE  GAGMANDANT=:GAGMANDANT: AND GAGART=:GAGART: AND GAGVAR =:GAGVAR: AND GAGFARBE=:GAGFARBE: AND GAGPOS=:GAGPOS:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void DGG02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();

            or1.CommandText = "DELETE FROM GASTGA "
                + " WHERE  GAGMANDANT=:GAGMANDANT: AND GAGART=:GAGART: AND GAGVAR =:GAGVAR: AND GAGFARBE=:GAGFARBE:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void SaveGASTGA(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryGASTGA(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }

            MapGASTGA(ref dwLocal, dwData);

            if (!flgExist)
            {
                InsertGASTGA(dwLocal);
            }
            UpdateGASTGA(dwLocal);
        }
        public void InsertGASTGA(DataRow dwData, string ITXDBVERSION = "50.24")
        {

            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                case "TST":
                case "SXN":
                case "UBT":
                default:
                    or1.CommandText = "INSERT INTO GASTGA ( GAGMANDANT,GAGART,GAGVAR,GAGFARBE,GAGPOS,GAGGART,GAGGVAR,GAGGFARBE,GAGABF,GAGKGNET,GAGKGBRUT,GAGDISPOKZ,GAGBEM,GAGFDZANZ,GAGANTEIL,GAGBASIS,GAGFILLER)"
                + " VALUES (:GAGMANDANT:,:GAGART:,:GAGVAR:,:GAGFARBE:,:GAGPOS:,:GAGGART:,:GAGGVAR:,:GAGGFARBE:,:GAGABF:,:GAGKGNET:,:GAGKGBRUT:,:GAGDISPOKZ:,:GAGBEM:,:GAGFDZANZ:,:GAGANTEIL:,:GAGBASIS:,:GAGFILLER:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case "FLT":
                    or1.CommandText = "INSERT INTO GASTGA ( GAGMANDANT,GAGART,GAGVAR,GAGFARBE,GAGPOS,GAGGART,GAGGVAR,GAGGFARBE,GAGABF,GAGKGNET,GAGKGBRUT,GAGDISPOKZ,GAGBEM,GAGFDZANZ,GAGANTEIL,GAGBASIS,GAGFILLER)"
                    + " VALUES (:GAGMANDANT:,:GAGART:,:GAGVAR:,:GAGFARBE:,:GAGPOS:,:GAGGART:,:GAGGVAR:,:GAGGFARBE:,:GAGABF:,:GAGKGNET:,:GAGKGBRUT:,:GAGDISPOKZ:,:GAGBEM:,:GAGFDZANZ:,:GAGANTEIL:,:GAGBASIS:,:GAGFILLER:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet QueryGASTGA(string yart, string yvar, string ycolor)
        {

            DataSet Result;
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM GASTGA WHERE   GAGMANDANT=:GAGMANDANT AND GAGART=:GAGART AND GAGVAR=:GAGVAR AND GAGFARBE=:GAGFARBE";
            or1.Parameters.Add("GAGMANDANT", OracleType.NVarChar).Value = "00";
            or1.Parameters.Add("GAGART", OracleType.NVarChar).Value = yart;
            or1.Parameters.Add("GAGVAR", OracleType.NVarChar).Value = yvar;
            or1.Parameters.Add("GAGFARBE", OracleType.NVarChar).Value = ycolor;

            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
            {
                Result.Tables[0].TableName = "GASTGA";
            }
            return Result;
        }
        public DataSet QueryGASTGA(string yart)
        {

            DataSet Result;
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT GAGART,GAGVAR,GAGFARBE FROM GASTGA WHERE  GAGMANDANT=:GAGMANDANT AND GAGART=:GAGART";
            or1.Parameters.Add("GAGMANDANT", OracleType.NVarChar).Value = "00";
            or1.Parameters.Add("GAGART", OracleType.NVarChar).Value = yart;
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
            {
                Result.Tables[0].TableName = "GASTGA";
            }

            return Result;
        }
        public DataSet QueryGASTGA(DataRow dwData, int option = 1)
        {


            switch (option)
            {
                case 0:
                    SResult = QGG00(dwData);
                    break;
                case 1:
                    SResult = QGG01(dwData); break;
                case 2:
                    SResult = QGG02(dwData);
                    break;
                case 3:
                    SResult = QGG03(dwData);
                    break;
                case 4:
                    SResult = QGG04(dwData);
                    break;
                case 5:
                    SResult = QGG05(dwData);
                    break;
                case 6:
                    SResult = QGG06(dwData);
                    break;
                case 7:
                    SResult = QGG07(dwData);
                    break;
                case 8:
                    SResult = QGG08(dwData);
                    break;
                case 9:
                    SResult = QGG09(dwData);
                    break;
                default:
                    break;
            }
            return SResult;
        }
        public DataSet QGG00(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM GASTGA WHERE  ROWNUM<2 ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QGG01(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM GASTGA "
                +" WHERE GAGMANDANT=:GAGMANDANT: AND GAGART=:GAGART: AND GAGVAR=:GAGVAR: AND GAGFARBE=:GAGFARBE: AND GAGPOS=:GAGPOS:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QGG02(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM GASTGA WHERE  GAGMANDANT=:GAGMANDANT: AND GAGART=:GAGART:";
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QGG03(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM GASTGA WHERE  "
                    + "  GAGMANDANT=:GAGMANDANT: AND GAGART=:GAGART: AND GAGVAR=:GAGVAR: AND GAGFARBE=:GAGFARBE:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QGG04(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT M.*, NVL(GSFERTKZ,' ') AS GSFERTKZ FROM GASTGA M LEFT JOIN GSDAT ON GAGGART=GSART AND GAGGVAR=GSVAR WHERE  GAGMANDANT=:GAGMANDANT: AND GAGART=:GAGART: AND GAGVAR=:GAGVAR: AND GAGFARBE=:GAGFARBE:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QGG05(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT M.*, NVL(GSFERTKZ,' ') FROM GASTGA M LEFT JOIN GSDAT ON GAGGART=GSART AND GAGGVAR=GSVAR  "
                    + " WHERE GAGMANDANT=:GAGMANDANT: AND GAGART=:GAGART: AND GAGVAR=:GAGVAR:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QGG06(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT GAAUFKRS,GAAUFLFD,GAAUFPOS,M.* FROM VKPOS JOIN GAAUF ON VKPSA='  ' AND VKPMANDANT=GAMANDANT AND VKPAUFKRS=GAAUFKRS AND VKPAUFLFD=GAAUFLFD AND VKPAUFPOS=GAAUFPOS AND GASA=610"
            + " JOIN GASTGA M ON  GAGART=GAART "
                + "WHERE VKPMANDANT=:VKPMANDANT: AND VKPAUFKRS=:VKPAUFKRS: AND VKPAUFLFD=:VKPAUFLFD: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QGG07(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT GAAUFKRS,GAAUFLFD,GAAUFPOS,M.* FROM VKPOS JOIN GAAUF ON VKPSA='  ' AND VKPMANDANT=GAMANDANT AND VKPAUFKRS=GAAUFKRS AND VKPAUFLFD=GAAUFLFD AND VKPAUFPOS=GAAUFPOS AND GASA=610"
            + " JOIN GASTGA M ON  GAGART=GAART "
                + "WHERE VKPMANDANT=:VKPMANDANT: AND VKPAUFKRS=:VKPAUFKRS: AND VKPAUFLFD=:VKPAUFLFD: AND VKPAUFPOS=:VKPAUFPOS: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QGG08(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT M.*, NVL(GSFERTKZ,' ') FROM GASTGA M  LEFT JOIN GSDAT ON GAGGART=GSART AND GAGGVAR=GSVAR  "
                    + " WHERE GAGMANDANT=:GAGMANDANT: AND GAGART=:GAGART:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QGG09(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();

            or1.CommandText = "SELECT M.*, NVL(GSFERTKZ,' ') FROM GASTGA M  LEFT JOIN GSDAT ON GAGGART=GSART AND GAGGVAR=GSVAR  "
                    + " WHERE GAGMANDANT=:GAGMANDANT: AND GAGART=:GAGART: AND GAGVAR=:GAGVAR:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QGG10(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM GASTGA P   "
                    + " WHERE GAGMANDANT=:GAGMANDANT: AND GAGART=:GAGART: AND GAGVAR=:GAGVAR: AND GAGFARBE=:GAGFARBE:"
                    +dwData["GAGPOS"].ToString()
                    + " ORDER BY GAGART,GAGVAR,GAGFARBE "
                    ;
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QGG11(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT NVL(MAX(GAGPOS),'00') AS POS FROM GASTGA P   "
                    + " WHERE GAGMANDANT=:GAGMANDANT: AND GAGART=:GAGART: AND GAGVAR=:GAGVAR: AND GAGFARBE=:GAGFARBE:"                   
                    + " ORDER BY GAGART,GAGVAR,GAGFARBE";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public void UpdateGASTGA(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {

            if (option == 0)
            {
                switch (app.company)
                {
                    default:
                    case "FLT":
                    case "JLT":
                        UGG01(dwData);
                        break;
                    case "YGR":
                    case "TST":
                    case "SXN":
                    case "UBT":
                        UGG02(dwData);
                        break;

                }
            } 

        }
        public void UGG01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE GASTGA SET  GAGMANDANT=:GAGMANDANT:,GAGART=:GAGART:,GAGVAR=:GAGVAR:,GAGFARBE=:GAGFARBE:,GAGPOS=:GAGPOS:,GAGGART=:GAGGART:,GAGGVAR=:GAGGVAR:,GAGGFARBE=:GAGGFARBE:,GAGABF=:GAGABF:,GAGKGNET=:GAGKGNET:,GAGKGBRUT=:GAGKGBRUT:,GAGDISPOKZ=:GAGDISPOKZ:,GAGBEM=:GAGBEM:,GAGFDZANZ=:GAGFDZANZ:,GAGANTEIL=:GAGANTEIL:,GAGBASIS=:GAGBASIS:,GAGFILLER=:GAGFILLER: "
       + " WHERE GAGMANDANT=:GAGMANDANT: AND GAGART=:GAGART:  AND GAGVAR=:GAGVAR:  AND GAGFARBE=:GAGFARBE: AND GAGPOS=:GAGPOS:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void UGG02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE GASTGA SET GAGMANDANT=:GAGMANDANT:,GAGART=:GAGART:,GAGVAR=:GAGVAR:,GAGFARBE=:GAGFARBE:,GAGPOS=:GAGPOS:,GAGGART=:GAGGART:,GAGGVAR=:GAGGVAR:,GAGGFARBE=:GAGGFARBE:,GAGABF=:GAGABF:,GAGKGNET=:GAGKGNET:,GAGKGBRUT=:GAGKGBRUT:,GAGDISPOKZ=:GAGDISPOKZ:,GAGBEM=:GAGBEM:,GAGFDZANZ=:GAGFDZANZ:,GAGANTEIL=:GAGANTEIL:,GAGBASIS=:GAGBASIS:,GAGFILLER=:GAGFILLER:"

             + " WHERE GAGMANDANT=:GAGMANDANT: AND GAGART=:GAGART:  AND GAGVAR=:GAGVAR:  AND GAGFARBE=:GAGFARBE: AND GAGPOS=:GAGPOS:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
    }
}
